package org.abratuhi.uradar.model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

import org.abratuhi.uradar.util.ResponseUtil;

/**
 * Special kind of URadarModule suitable for different social networks. May contain data, referring direct
 * to the user, like date of birth, age, name; and a friends' list with visibility option as integer. The general
 * rule is "One may get information with visibility value greater-or-equal to visibility value in friends' list".
 * <p>
 * Visibility=0 is the 'root'-visibility (0 - is the smallest value allowed), meaning with visibility=0, one may
 * get all the information about this user.
 * <p>
 * Notice: all URadar modules must extend either URadarModule or URadarUpdateModule.
 * <p>
 * Notice: when extending this class, one has less flexibility in storing data.
 * <p>
 * Notice: contains only following two tables, which are to be created manually, when calling the constructor of inheriting class.
 * <ul>
 * 	<li>modulename(uradarid, moduleid, ...)
 * 	<li>modulename_friends(uradarid, uradarid_friend, visibility)
 * </ul>
 * 
 * 
 * @author Alexei Bratuhin
 * @version 1.00
 */
public class URadarUpdateModule extends URadarModule{
	
	/**
	 * Constructor.
	 * <p>
	 * Notice: doesn't create MySQL tables by itself - createTables() must be called. See existing module implementations for examples.
	 * @param name				name of the module, mostly lower-case
	 * @param description		short description of the module
	 * @param props				module-specific properties
	 * @param connection		from URadar inherited connection to MySQL DB
	 */
	public URadarUpdateModule(String name, String description,
			Properties props) {
		// super
		super(name, description, props);
	}

	/**
	 * Find user in DB using a module-specific id (e.g. Facebook ID, SecondLife ID).
	 * <p>
	 * Notice: a.k.a. findUser(String moduleID).
	 * @param reqprops		HTTP GET query
	 * @return				<ul><li>user's U(niversal)Radar ID, if found in DB</li>
	 * 						<li>INVALID_MODULE_ID, if not found in DB</li>
	 * 						<li>FAIL, otherwise</li></ul>
	 */
	public String resolveModuleID(Properties reqprops){
		// init return/response string
		String out = new String();
		// get request details
		String moduleID = reqprops.getProperty("moduleid");
		// try to connect to DB for search
		try{
			// open connection
			Connection connection = openConnection();
			// create statement
			Statement stmt = connection.createStatement();
			// generate query string
			String sql_check_moduleid = "select uradarid from "+this.name+" where moduleid='"+moduleID+"';";
			// query DB, check whether moduleID isRegistered
			ResultSet rs_check = stmt.executeQuery(sql_check_moduleid);
			// check size of result -> if resultset is empty no resolution is possible
			if(rs_check.first()){
				// return
				out = rs_check.getString("uradarid");
			}
			else{
				// return
				out = INVALID_ID;
			}
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			// close connection
			closeConnection(connection);

		} catch (SQLException e){
			e.printStackTrace();
			out = FAIL;
		}
		// return
		return out;
	}
	
	/**
	 * Add/Update user information in MySQL table of module using module-specific information from request query.
	 * @param reqprops			HTTP GET query
	 * @return					<ul><li>OK, if add/updated successfully</li>
	 * 							<li>FAIL, otherwise</li></ul>
	 */
	public String addupdateUser(Properties reqprops){
		// 
		String out = new String();
		//
		String myURadarID = reqprops.getProperty("uradarid");
		try{
			Connection connection = openConnection();
			// create statement
			Statement stmt = connection.createStatement();
			// generate query string
			String modulenameid = reqprops.getProperty("moduleid"); // second obligatory field
			String sql_addupdate_user = "insert into "+this.name+"(uradarid, moduleid) values ('"+myURadarID+"','"+modulenameid+"');";
			System.out.println("URadarUpdateModule addupdateUser():\t"+sql_addupdate_user);
			// execute query
			stmt.executeUpdate(sql_addupdate_user);
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			out = OK;
			
			closeConnection(connection);
		}catch (SQLException e){
			e.printStackTrace();
			out = FAIL;
		}
		return out;
	}

	/**
	 * Add/Update user's friend information in MySQL table of module using module-specific information from request query.
	 * @param reqprops			HTTP GET query
	 * @return					<ul><li>OK, if addupdated successfully</li>
	 * 							<li>FAIL, otherwise</li></ul>
	 */
	public String addupdateFriend(Properties reqprops){
		// get request needed properties
		String myURadarID;// = reqprops.getProperty("uradarid");
		String friendURadarID;// = reqprops.getProperty("uradarid_friend");
		// uradarid
		if(reqprops.containsKey(new String("uradarid"))){
			myURadarID = reqprops.getProperty("uradarid");
		}
		else{
			myURadarID = resolveModuleID(reqprops);
		}
		//uradarid friend
		if(reqprops.containsKey("uradarid_friend")){
			friendURadarID = reqprops.getProperty("uradarid_friend");
		}
		else{
			friendURadarID = resolveModuleID(reqprops);
		}
		
		// check resolution
		if(myURadarID.equals(INVALID_ID) || friendURadarID.equals(INVALID_ID)){
			return FAIL;
		}
		
		// proceed request
		try{
			// check whether add or update
			boolean add = false;
			boolean update = false;
			Connection connection = openConnection();
			Statement stmt0 = connection.createStatement();
			String sql_check = "select * " +
								"from "+this.name+"_friends " +
								"where (uradarid='"+myURadarID+"' and uradarid_friend='"+friendURadarID+"');";
			ResultSet rs_check = stmt0.executeQuery(sql_check);
			if(rs_check.first()){
				update = true;
			}
			else{
				add=true;
			}
			stmt0.close();
			// addupdate
			// create statement
			Statement stmt = connection.createStatement();
			// generate query string
			String visibility = reqprops.getProperty("visibility"); // third obligatory field
			String sql_addupdate_friend = new String();
			if(add) sql_addupdate_friend = "insert into "+this.name+"_friends"+"(uradarid, uradarid_friend, visibility)" +
											" values ('"+myURadarID+"','"+friendURadarID+"','"+visibility+"');";
			if(update) sql_addupdate_friend = "update "+this.name+"_friends " +
												"set visibility='"+visibility+"' " +
												"where (uradarid='"+myURadarID+"' and uradarid_friend='"+friendURadarID+"');";
			// execute query
			stmt.executeUpdate(sql_addupdate_friend);
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			
			closeConnection(connection);
			// 
			return OK;
		}catch (SQLException e){
			e.printStackTrace();
			return FAIL;
		}
	}

	/**
	 * Get module-specific information about user.
	 * @param reqprops			HTTP GET query
	 * @return					<ul><li>XML-string representing module-specific information about user</li>
	 * 							<li>FAIL, otherwise</li></ul>
	 */
	public String getOwnInfo(Properties reqprops){
		// init return/response string
		String out = new String();
		//
		String myURadarID = reqprops.getProperty("uradarid");
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			// create statement
			Statement stmt = connection.createStatement();
			// generate query string
			String sql_get_own_info = "select * from "+this.name+" where uradarid='"+myURadarID+"';";
			// query DB for own info
			ResultSet rs_get_own_info = stmt.executeQuery(sql_get_own_info);
			// generate return/response string
			out = ResponseUtil.convertResultSet2XMLString(rs_get_own_info);
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			closeConnection(connection);
		} catch (SQLException e){
			e.printStackTrace();
			out = FAIL;
		}
		// return
		return out;
	}
	
	/**
	 * Get module-specific information about user from another module.
	 * @param reqprops		HTTP GET query
	 * @return				<ul><li>XML-string representing module-specific information about user from another module</li>
	 * 						<li>INVALID_MODULE_ID, if didn't find suitable module to forward request to, due to name ot type mismatch</li></ul>
	 */
	public String getOwnInfoFromModule(Properties reqprops){
		// get request info
		String modulename = reqprops.getProperty("reqmodulefrom");
		// proceed request
		URadarModule module = URadar.getInstance().findModule(modulename);
		if(module != null && (module instanceof URadarUpdateModule)){
			return ((URadarUpdateModule) module).getOwnInfo(reqprops);
		}
		else{
			return INVALID_MODULE_ID;
		}
	}
	
	/**
	 * Get array of user's friends' URadarIDs.
	 * @param myURadarID		own URadarID
	 * @return		array of URadarIDs of friends, which might also be an empty array, if user has no friends
	 */
	public String[] getFriendsURadarID(String myURadarID){
		// init return/response string
		ArrayList<String> out = new ArrayList<String>();
		
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			
			// create statement
			Statement stmt = connection.createStatement();
			
			// generate query string
			//String sql_get_friends = "select distinct uradarid_friend as uradarid from "+(this.name+"_friends")+" where uradarid='"+myURadarID+"' or select distinct uradarid from "+(this.name+"_friends")+" where uradarid_friend='"+myURadarID+"');";
			String sql_get_friends = "select distinct uradarid_friend as uradarid from "+(this.name+"_friends")+" where uradarid='"+myURadarID+"';";
			
			// query DB for own info
			ResultSet rs_get_friends = stmt.executeQuery(sql_get_friends);

			// get results to output array
			if(rs_get_friends.first()){
				do{
					out.add(rs_get_friends.getString("uradarid"));
				}
				while (rs_get_friends.next());
			}
			
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			
			closeConnection(connection);

		} catch (SQLException e){
			e.printStackTrace();
		}
		// return null if connection to DB couldn't be established
		return out.toArray(new String[out.size()]);
	}

	/**
	 * Get module-specific information about user's friends.
	 * @param reqprops			user's U(niversal)Radar ID
	 * @return					<ul><li>XML-string representing module-specific information about user's friends</li>
	 * 							<li>FAIL, if connection to DB couldn't been established</li></ul>
	 */
	public String getFriendsInfo(Properties reqprops){
		// init return/response string
		String out = new String();
		//
		String myURadarID = reqprops.getProperty("uradarid");
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			
			// create statement
			Statement stmt = connection.createStatement();
			
			// generate query string
			String sql_get_friends_info = "select * from "+this.name+" where uradarid in (select distinct uradarid_friend from "+(this.name+"_friends")+" where uradarid='"+myURadarID+"');";
			
			// query DB for own info
			ResultSet rs_get_friends_info = stmt.executeQuery(sql_get_friends_info);
			
			// check size of result -> if resultset is empty no resolution is possible
			out = ResponseUtil.convertResultSet2XMLString(rs_get_friends_info);
				
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			
			closeConnection(connection);

		} catch (SQLException e){
			e.printStackTrace();
			out = FAIL;
		}
		// return
		return out;
	}
	
	/**
	 * Get module-specific information about user's friends.
	 * <p>
	 * Notice: usually used out-module
	 * @param myURadarID			user's U(niversal)Radar ID
	 * @param friendsURadarID		array of friends' URadarIDs
	 * @return						<ul><li>XML-string representing module-specific information about user's friends</li>
	 * 								<li>FAIL, if connection to DB couldn't been established</li></ul>
	 */
	public String getFriendsInfo(String myURadarID, String[] friendsURadarID){
		// init return/response string
		String out = new String();
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			
			// create statement
			Statement stmt = connection.createStatement();
			
			// generate query string
			String sql_get_friends_info = "";
			sql_get_friends_info += "select * from "+this.name+" where uradarid in ('";
			for(int i=0; i<friendsURadarID.length-1; i++){
				sql_get_friends_info += friendsURadarID[i] + "', '";
			}
			sql_get_friends_info += friendsURadarID[friendsURadarID.length-1];
			sql_get_friends_info += "');";
			
			// query DB for own info
			ResultSet rs_get_friends_info = stmt.executeQuery(sql_get_friends_info);
			
			//
			out = ResponseUtil.convertResultSet2XMLString(rs_get_friends_info);
				
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			
			closeConnection(connection);

		} catch (SQLException e){
			e.printStackTrace();
			out = FAIL;
		}
		// return
		return out;
	}
	
	/**
	 * Get module-specific information about user's friends from antoher module.
	 * @param reqprops		HTTP GET query
	 * @return				<ul><li>XML-string representing module-specific information about user's friends from another module</li>
	 * 						<li>INVALID_MODULE_ID, if request couldn't been sent to another module due to inability to find the module specified in request</li></ul>
	 */
	public String getFriendsInfoFromModule(Properties reqprops){
		// get request info
		String myURadarID = reqprops.getProperty("uradarid");
		String[] friendsURadarID = getFriendsURadarID(myURadarID);
		String modulename = reqprops.getProperty("reqmodulefrom");
		// proceed request
		URadarModule module = URadar.getInstance().findModule(modulename);
		if(module != null && (module instanceof URadarUpdateModule)){
			return ((URadarUpdateModule) module).getFriendsInfo(myURadarID, friendsURadarID);
		}
		else{
			return INVALID_MODULE_ID;
		}
	}

	/**
	 * Remove user from users of this module.
	 * @param reqprops			HTTP GET query
	 * @return					<ul><li>OK, if uses successfully removed</li>
	 * 							<li>FAIL, otherwise</li></ul>
	 */
	public String removeUser(Properties reqprops){
		//
		String myURadarID = reqprops.getProperty("uradarid");
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			
			// create statement
			Statement stmt = connection.createStatement();
			// generate query strings
			String sql_delete_user = "delete  from "+this.name+" where uradarid='"+myURadarID+"';";	// clean <modulname> table
			String sql_delete_friends = "delete  from "+this.name+"_friends"+" where uradarid='"+myURadarID+"';";	// clean <modulname>_friends table
			// delete friends
			stmt.executeUpdate(sql_delete_friends);
			// delete user
			stmt.executeUpdate(sql_delete_user);
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			
			closeConnection(connection);
			// return 
			return OK;
		} catch (SQLException e){
			e.printStackTrace();
			return FAIL;
		}
	}

	/**
	 * Remove user's friend from friends relationship table.
	 * @param reqprop			HTTP GET query
	 * @return					<ul><li>OK, if friend successfully removed</li>
	 * 							<li>FAIL, otherwise</li></ul>
	 */
	public String removeFriend(Properties reqprops){
		//
		String myURadarID = reqprops.getProperty("uradarid");
		String friendURadarID = reqprops.getProperty("uradarid_friend");
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			
			// create statement
			Statement stmt = connection.createStatement();
			
			// generate query string
			String sql_delete_friend = "delete  from "+this.name+"_friends"+" where uradarid='"+myURadarID+"' and uradarid_friend='"+friendURadarID+"';";
			
			// delete friend
			stmt.executeUpdate(sql_delete_friend);
			
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			
			closeConnection(connection);
			
			// return
			return OK;
		} catch (SQLException e){
			e.printStackTrace();
			return FAIL;
		}
	}

	@Override
	public String proceedRequest(Properties reqprops) {
		// init response string
		String response = new String();
		// get request type
		String reqtype = reqprops.getProperty("reqtype");
		// switch
		if(reqtype.equals("resolve_module_id")){
			response = resolveModuleID(reqprops);
			return response;
		}
		if(reqtype.equals("addupdate_user")){
			response = addupdateUser(reqprops);
			return response;
		}
		if(reqtype.equals("addupdate_friend")){
			response = addupdateFriend(reqprops);
			return response;
		}
		if(reqtype.equals("get_own_info")){
			response = getOwnInfo(reqprops);
			return response;
		}
		if(reqtype.equals("get_friends_info")){
			response = getFriendsInfo(reqprops);
			return response;
		}
		if(reqtype.equals("get_own_info_from_module")){
			return getOwnInfoFromModule(reqprops);
		}
		if(reqtype.equals("get_friends_info_from_module")){
			return getFriendsInfoFromModule(reqprops);
		}
		if(reqtype.equals("remove_user")){
			response = removeUser(reqprops);
			return response;
		}
		if(reqtype.equals("remove_friend")){
			response = removeFriend(reqprops);
			return response;
		}
		// if no match in switch block		
		response = FAIL;
		return response;
	}

}
